<?php
/**
 * Created by PhpStorm.
 * User: CQH
 * Date: 2017/9/25
 * Time: 10:00
 */

namespace app\operation\controller;

//use think\Cache;
use think\Controller;
use app\common\model\Customer;
use app\common\model\CustomerAdministrativeCenter;
use app\common\model\Contract;
use think\Exception;
use think\Request;
use think\Db;
use app\common\model\CustomerLog;
use app\common\model\Administrative;

/*
 * 客户管理  运营中心
 */
class Customerinfo extends Base{
	
	protected  $Customer;
	protected  $Cacenter;
	protected  $Customerlog;
	protected  $adinfo;
	public function __construct(){
		parent::__construct();
		//实例化客户model
		$this->Customer = new Customer();
		$this->Cacenter = new CustomerAdministrativeCenter();
		$this->Customerlog = new CustomerLog();
		$this->adinfo = new Administrative();
	}
	
	/**
     * 客户列表
     * @Request array    $request     GET   搜索条件(一维数组)
     * @return array
     */
	public function index(){
		//Cache::clear();
        //处理条件
        $request = input('');
        $where = array();
        $search = array(
        	'username'=>'',
        	'tel'=>'',
            'telphone'=>'',
            'id_card'=>'',
            'remark'=>''
        );
        foreach ($request as $key => $value){
            $search[$key] = $value;
            if (empty($value)){
                continue;
            }
            if ($key == 'username'){
            	$where['username'] = ['like','%'.$value.'%'];
            	continue;
            }
            if ($key == 'tel'){
            	$where['rq_customer.telphone'] = $value;
            	continue;
            }
            if ($key == 'remark'){
                $where['rq_customer.remark'] = ['like','%'.$value.'%'];
                continue;
            }
            if ($key == 'user_type'){
                $where['rq_customer.'.$key] = $value;
                continue;
            }
            if ($key == 'is_entry'){
                $where['rq_customer.'.$key] = $value;
                continue;
            }
            if ($key == 'operation_id'){
                $where['rq_customer.'.$key] = $value;
                continue;
            }
            if ($key == 'administrative_id'){
                $where['rq_customer_administrative_center.'.$key] = $value;
                continue;
            }
            $where[$key] = $value;
        }
        
        $where['operation_id'] = session($this->set['db_name'].'o_id');
        //获取数据
        $data = $this->Customer->selectPage($where);
        //获取运营中心
        $operation = $this->Customer->selectOperationInfo(array('op.o_id'=>session($this->set['db_name'].'o_id')));
//        for ($i=count($operation); $i >= 0; $i--) {
//            if($i>=1){
//                if($operation[$i]=$operation[$i-1]){
//                    unset($operation[$i]);
//                }
//            }
//        }
        //获取行政中心
        $ainfo = $this->Customer->selectAdministrativeInfo(array('operation'=>session($this->set['db_name'].'o_id')));
        
        

        //模板赋值
        $this->assign('Nowpage', $data['page']['currentPage']); //当前页
        $this->assign('allpage', $data['page']['lastPage']); //总页数
        $this->assign('count', $data['page']['count']);//总条数
        $this->assign('search',$search);
        $this->assign('operation',$operation);
        $this->assign('ainfo',$ainfo);
        $this->assign('data',$data['data']);
        if(input('get.page')){
            return json($data);
        }
        //渲染模板
        return $this->fetch('admin@customer:index');
	}
	
	
	/**
	 * 删除客户     逻辑删除
	 * @Request user_id             int    GET   客户ID
	 * @return array
	 */
	public function delete()
	{
		//检验get参数
		if (input('?get.data')){
			//获取参数并强制转换为int型
			$user_id = input('')['data'];
		}else{
			return json(array('code'=>0,'msg'=>'缺少必要参数'));
		}
	
		foreach ($user_id as $key => $value){
			if ($value == 'on'){
				continue;
			}
	
			$checkData = $this->Customer->selectOne(array('user_id'=>intval($value)));
	
			if (empty($checkData)){
				return json(array('code'=>0,'msg'=>"删除失败"));
			}
			$res = $this->Customer->deleteOne($value);
			if($res === false){
				return json(array('code'=>0,'msg'=>'删除失败'));
			}
		}
		return json(array('code'=>1,'msg'=>'删除成功'));
	}
	
	
    /**
     * 查看用户详情
     * @Request user_id             int    GET   用户ID
     * @return array
     */
    public function details()
    {
    	
    	$where = array();
        //检验get参数
        if (input('?get.user_id')){
            //获取参数并强制转换为int型
            $where['rq_customer.user_id'] = input('get.user_id/d');
        }else{
            return json(array('code'=>0,'msg'=>'缺少必要参数'));
        }
        
        //获取数据
        $info = $this->Customer->findOne($where);
        $log = $this->Customerlog->getList(array('customer_id'=>$info['user_id']));//运营中心查看log
        if ($log){
        	foreach ($log as $k=>$v){
        		$t = $log[$k]['add_time'];
        		$log[$k]['add_time'] = date("Y-m-d H:i:s",$t);
        	}
        	$this->assign('log',$log);
        }

       	//$info[0]['uid'] = session($this->set['db_name'].'uid');
       	//$info[0]['name'] = session($this->set['db_name'].'username');
       	//$info[0]['rolename'] = session($this->set['db_name'].'rolename');

       	/*
       	 * 合同
       	 * @author  ligang
       	 */
        $Contract = new Contract();
        $ContractWhere['user_id'] = $where['rq_customer.user_id'];
        $ContractWhere['is_delete'] = 0;
        $ContractArray = $Contract->selectData($ContractWhere,'contract_id,contract_no,installed_time');
        $i = 1;
        foreach ($ContractArray as $key =>$value){
            $ContractArray[$key]['No'] = $i;
            $ContractArray[$key]['EquipmentNumber'] = $Contract->getEquipmentNumber(['contract_id'=>$value['contract_id'],'state'=>1,'is_delete'=>0]);
            $ContractArray[$key]['installed_time'] = date('Y.m.d',$value['installed_time']);
            $i++;
        }
        $this->assign('Contract',$ContractArray);
       	//---------------------
        $this->assign('info',$info);
        

        //渲染模板
        return $this->fetch('admin@customer:details');

    }
	
    /**
     * 显示新增客户页面
     * @Request user_id             int    GET   客户ID
     * @return array
     */
    public function add()
    {	
    	//Cache::clear();
    	$oid = session('operation_usero_id');
    	//获取运营中心
    	$operation = $this->Customer->selectOperationInfo(array('op.o_id'=>$oid));
    	//获取当前运营中心下面的行政中心
        $atwhere['operation'] = $oid;
    	$administrative = $this->Customer->selectAdministrativeInfo($atwhere);

    	//获取地址
    	$province = $this->Customer->areasProvince(array('id'=>$operation[0]['province_id']));
    	$city = $this->Customer->areasCity($operation[0]['province_id']);
    	$area = $this->Customer->areaName($city[0]['id']);
    	
    	//获取客户配置信息
    	$status = config('account_status');
    	$nature = config('nature');
    	$housing_property = config('housing_property');
    	$is_entry = config('is_entry');
    	$user_type = config('user_type');
    	unset($is_entry['3']);
    	
    	$this->assign('operation',$operation);
    	$this->assign('administrative',$administrative);
    	
    	$this->assign('province',$province);
    	$this->assign('city',$city);
    	$this->assign('area',$area);
    	
    	$this->assign('status',$status);
    	$this->assign('nature',$nature);
    	$this->assign('housing_property',$housing_property);
    	$this->assign('is_entry',$is_entry);
    	$this->assign('user_type',$user_type);

    	//渲染模板
        return $this->fetch('admin@customer:add_user');
    }	 
    
    /**
     * 处理 新增客户信息       （没有离职状态）                   
     * @Request user_id             int    GET   客户ID
     * @return array
     */
    public function doadd()
    {
    	//Cache::clear();
    	$where = array();
    	$field = array();
    	//检验提交数据
    	$data = $this->request->post();
    	//去除空格
    	foreach ($data as $k=>$v){
    		$data[$k] = preg_replace("/\s|　/","",$v);
    	}
    	//验证数据
        $vRet = $this->Customer->valli($data);
    	if($vRet!==true){
    	    return json($vRet);
        }

        //获取地址名
        $areaInfo = db('areas')->where('id','in',[$data['province_code'],$data['city_code'],$data['area_code']])->select();
        $data['province'] = $areaInfo[0]['area_name'];
        $data['city'] = $areaInfo[1]['area_name'];
        $data['area'] = $areaInfo[2]['area_name'];

//        //判断电话号码唯一
//        $sWhere['telphone'] = $data['telphone'];
//        $cuInfo = $this->Customer->findOne($sWhere);
//        if($cuInfo){
//            return json(array('code'=>0,'msg'=>'联系电话已经存在','data'=>''));
//        }
//        //判断身份证号码唯一
//        $idWhere['rq_customer.user_id'] = $data['id_card'];
//        $cuInfo = $this->Customer->findOne($idWhere);
//        if($cuInfo){
//            return json(array('code'=>0,'msg'=>'身份证已经存在','data'=>''));
//        }
        //给运营中心固定值
        $data['operation_id'] = session($this->set['db_name'].'o_id');
        
    	//处理数据
    	$a_id = $data['a_id'];
    	$data['birthday']=strtotime($data['birthday']);
    	$data['avatar_img']='';
    	$data['add_time'] = time();
    	
    	unset($data['a_id']);
    	//判断是否入职
    	if ($data['is_entry']==1){

    		DB::startTrans();
    		try{
                //入职
                $data['personnel_time']=strtotime($data['personnel_time']);
                $re=Db::name('personnel_number')->insert(['personnel_number'=>$data['employee_id'],'personnel_time'=>$data['personnel_time']]);
                unset($data['personnel_time']);
                $user_id=$this->Customer->insertOne($data);
                $res=$this->Cacenter->insertOne(array('user_id'=>$user_id,'administrative_id'=>$a_id));
                DB::commit();
                if ($res == 0){
                    //日志
                    return json(array('code'=>0,'msg'=>'新增失败','data'=>''));
                }else{
                    //日志
                    return json(array('code'=>1,'msg'=>'新增成功','data'=>url('Customerinfo/index')));
                }
            }catch (Exception $e){
    		    DB::rollback();
                return json(array('code'=>0,'msg'=>'新增失败','data'=>''));
            }

    	}else{
    		//客户
            Db::startTrans();
            try{
                unset($data['personnel_time']);
                $user_id=$this->Customer->insertOne($data);
                $res=$this->Cacenter->insertOne(array('user_id'=>$user_id,'administrative_id'=>$a_id));
                Db::commit();
                if ($res == 0){
                    //日志
                    return json(array('code'=>0,'msg'=>'新增失败','data'=>''));
                }else{
                    //日志
                    return json(array('code'=>1,'msg'=>'新增成功','data'=>url('Customerinfo/index')));
                }
            }catch (Exception $e){
                Db::rollback();
                return json(array('code'=>0,'msg'=>'新增失败','data'=>''));
            }

    	}
    }
    
    
    
    /**
     * 修改客户信息   回显                 
     * @Request user_id             int    GET   客户ID
     * @return array
     */
    public function update()
    {
    	//Cache::clear();
    	$id = input('get.user_id/d');
    	$where = array('rq_customer.user_id' => $id);
    	$info = $this->Customer->findOne($where);

    	if(!$info){
    	    $this->error('数据错误');
        }
    	$this->assign('info',$info);

        $oid = session('operation_usero_id');
        //获取运营中心
        $operation = $this->Customer->selectOperationInfo(array('op.o_id'=>$oid));
//        for ($i=count($operation); $i >= 0; $i--) {
//            if($i>=1){
//                if($operation[$i]=$operation[$i-1]){
//                    unset($operation[$i]);
//                }
//            }
//        }
        //获取当前运营中心下面的行政中心
        $atwhere['operation'] = $oid;
        $administrative = $this->Customer->selectAdministrativeInfo($atwhere);

        // 获取用户信息
        $mbwhere['user_id'] = $id;
        $memberData = $this->Customer->selectOne($mbwhere);
        // 获取用户的地址
        $province = $this->Customer->areasProvince(array('id'=>$memberData[0]['province_code']));
        $city = $this->Customer->areasCity($memberData[0]['province_code']);
        $area = $this->Customer->areaName($memberData[0]['city_code']);

        //获取地址
//        $province = $this->Customer->areasProvince(array('id'=>$operation[0]['province_id']));
//        $city = $this->Customer->areasCity($operation[0]['province_id']);
//        $area = $this->Customer->areaName($city[0]['id']);

        
        //获取客户配置信息
    	$status = config('account_status');
    	$nature = config('nature');
    	$housing_property = config('housing_property');
    	$user_type = config('user_type');
        //$is_entry=[];
        $is_entry = config('is_entry');
        if(!empty($info['employee_id']) && $info['is_entry'] == '入职'){
            unset($is_entry);
            $is_entry = config('is_entry');
            foreach ($is_entry as $k => $v) {
                if($v=='客户'){
                    unset($is_entry[$k]);
                }
            }
        }        
        if(!empty($info['departure_time']) && $info['is_entry'] == '离职'){
            unset($is_entry);
            $is_entry = config('is_entry');
            foreach ($is_entry as $k => $v) {
                if($v=='离职'){
                    unset($is_entry[$k]);
                }
            }
        }
        $ty=db('customer')->where(['user_id'=>$id])->value('is_entry');
        if($ty==2){
            unset($is_entry);
            $is_entry = config('is_entry');
            foreach ($is_entry as $k => $v) {
                if($v=='离职'){
                    unset($is_entry[$k]);
                }
            }
        }
        $this->assign('operation',$operation);
        $this->assign('administrative',$administrative);
        
        $this->assign('province',$province);
        $this->assign('city',$city);
        $this->assign('area',$area);
        
        $this->assign('status',$status);
        $this->assign('nature',$nature);
        $this->assign('housing_property',$housing_property);
        $this->assign('is_entry',$is_entry);
        $this->assign('user_type',$user_type);

        //渲染模板
        return $this->fetch('admin@customer:update_user');

    }

    /**
     * 修改客户信息     
     * @Request user_id             int    GET   客户ID
     * @return array
     */
    public function doupdate()
    {

    	//Cache::clear();
    	$where = array();
    	//检验提交数据
    	$data = $this->request->post();
    	//查找原始数据
        $info = $this->Customer->findOne(array('rq_customer.user_id' => $data['user_id']));
        if(!$info){
            return json(array('code'=>0,'msg'=>'数据异常','data'=>''));
        }
        //去除空格
        foreach ($data as $k=>$v){
            $data[$k] = preg_replace("/\s|　/","",$v);
        }
        $vRet = $this->Customer->valli($data);
        if($vRet!==true){
            return json($vRet);
        }

        //获取地址名
        $areaInfo = db('areas')->where('id','in',[$data['province_code'],$data['city_code'],$data['area_code']])->select();
        $data['province'] = $areaInfo[0]['area_name'];
        $data['city'] = $areaInfo[1]['area_name'];
        $data['area'] = $areaInfo[2]['area_name'];
        //给运营中心固定值
        $data['operation_id'] = session($this->set['db_name'].'o_id');
		//dump($data);exit;
        Db::startTrans();
        try{
            foreach ($info as $key=>$val){
                if(isset($data[$key])){
                    if($data[$key]!= $val ){
                        //写入日志
                        $clogData['filename'] = $key;
                        $clogData['old_content'] = $val;
                        $clogData['new_content'] = $data[$key];
                        $clogData['operator_id'] = session($this->set['db_name'].'uid');//管理员ID
                        $clogData['operator_name'] = session($this->set['db_name'].'rolename');//超级管理员
                        $clogData['operator_account'] = session($this->set['db_name'].'username');//admin
                        $clogData['o_id'] = $data['operation_id'];
                        $clogData['a_id'] = '';
                        $clogData['terminal_type'] = 2;//终端信息 2为运营中心
                        $clogData['customer_id'] = $info['user_id'];//客户ID
                        $clogData['add_time'] = time();
                        $this->Customerlog->insertOne($clogData);
                    }
                }

            }
            //处理数据
            $a_id=$data['a_id'];
            $data['birthday']=strtotime($data['birthday']);
            if ($data['is_entry'] == 1 && $info['is_entry']!="入职") {
                $data['personnel_time'] = strtotime($data['personnel_time']);
            }
            unset($data['a_id']);
            //更新操作
            $where['user_id'] = $data['user_id'];
            //写入员工编号
            if ($info['is_entry']!="入职" && $data['is_entry']==1){
                $personnel_number = Db::table('rq_customer')->where(['employee_id'=>$data['employee_id']])->field('employee_id')->find();
                if (!empty($personnel_number)){
                    return json(array('code' => 0, 'msg' => '员工编号已存在', 'data' => ''));
                }
                Db::name('personnel_number')->insert(['personnel_number'=>$data['employee_id'],'personnel_time'=>$data['personnel_time']]);
            }
            if ($info['is_entry']!="离职" && $data['is_entry']==3){
                $idata['user_id'] =  $info['user_id'];
                $idata['employee_id'] =  $info['employee_id'];
                $idata['departure_time'] =  strtotime($data['departure_time']);
                Db::name('departure_time_log')->insert($idata);
            }

            unset($data['personnel_time']);
            if(isset($data['departure_time'])){
                $data['departure_time'] = strtotime($data['departure_time']);
            }
            $this->Customer->updateOne($where,$data);
            $this->Cacenter->updateOne($where,array('administrative_id'=>$a_id));
            // 提交事务
            Db::commit();
        } catch (\Exception $e) {
            //$earray = $e->getData();
            //file_put_contents('userlog.txt',$earray['Database Status']['Error SQL']);exit;
            // 回滚事务
            Db::rollback();
            return json(array('code'=>0,'msg'=>$e->getMessage(),'data'=>''));
        }

        // 判断客户是否离职
        if ($data['is_entry'] == 3) {
            $post_data['number'] = $data['employee_id'];
            // 业绩系统
            $url = config('post_entry_api');
            // 调用post_request发送请求
            $curResult = post_request($url, $post_data);
            $ApiResult = json_decode($curResult);
            if ($ApiResult->code == 1){
                return json(array('code'=>1,'msg'=>'修改成功','data'=>url('Customerinfo/index')));
            }else{
                return json(array('code'=>1,'msg'=>'迁移失败','data'=>url('Customerinfo/index')));
            }
        }

        return json(array('code'=>1,'msg'=>'修改成功','data'=>url('Customerinfo/index')));

    }
	
    
    
	/**
	 * 根据运营中心获取行政中心
	 * @Request operation_id             int    GET  
	 * @return array
	 */
	public function selectAdministrative()
	{
		$where = array();
		if(input('get.operation/d')){
			$where['operation'] = input('get.operation/d');
		}
		$administrative = $this->Customer->selectAdministrativeInfo($where);
		return $administrative;
	}

	/*
	 * 获取更多修改日志
	 */
	public function getUserLog(){
		//Cache::clear();
		$page = input('page');
		$limit = ($page*5).",".(5);
		$where['customer_id'] =  input('uid');
		$order="add_time desc";
		$listInfo = $this->Customerlog->getList($where,$order,$limit);
		if(!$listInfo){
			$data['code'] = 0;
			return json($data);
		}
		if ($listInfo){
			foreach ($listInfo as $k=>$v){
				$t = $listInfo[$k]['add_time'];
				$listInfo[$k]['add_time'] = date("Y-m-d H:i:s",$t);
			}
		}
		$data['page'] = $page+1;
		$data['data'] = $listInfo;
		$data['code'] = 1;
		return json($data);
	}

    //推荐设备详情
    public function devicedetails(){
        $cid = input('cid');
        $type = input('type',0);
        $introducerInfo = \db('customer')->where(['user_id'=>$cid])->find();

        if ($introducerInfo['is_entry'] == 1){
            //入职
            //获取入职时间
            $entry_time = Db::name('personnel_number')->where(['personnel_number'=>$introducerInfo['employee_id']])->field('personnel_time')->find()['personnel_time'];
            $cInfo = db('contract')
                ->where(array('co.introducer_id'=>$cid,'co.add_time'=>['<=',$entry_time]))
                ->alias('co')
                ->join('rq_customer cu','co.introducer_id=cu.user_id')
                ->select();
        }
        if ($introducerInfo['is_entry'] == 3){
            //离职
            $entry_time = Db::name('personnel_number')->where(['personnel_number'=>$introducerInfo['employee_id']])->field('personnel_time')->find()['personnel_time'];
            $leave_date = $introducerInfo['departure_time'];
            $where = [
                'co.introducer_id'=>$cid,
                'co.add_time'=>['NOT BETWEEN',[$entry_time,$leave_date]]
            ];
            $cInfo = db('contract')
                ->where($where)
                ->alias('co')
                ->join('rq_customer cu','co.introducer_id=cu.user_id')
                ->select();
        }

        if ($introducerInfo['is_entry'] == 2){
            $cInfo = db('contract')
                ->where(array('co.introducer_id'=>$cid,'is_entry'=>['<>',1]))//
                ->alias('co')
                ->join('rq_customer cu','co.introducer_id=cu.user_id')
                ->select();
        }

        $data = array();
        $count = 0;
        foreach ($cInfo as $key=>$val){
            $contract_id = $val['contract_id'];
            $temp =  db('contract_equipment')
                ->join([['rq_contract','rq_contract_equipment.contract_id = rq_contract.contract_id'],['rq_customer','rq_customer.user_id = rq_contract.user_id']])
                ->where(array('rq_contract_equipment.contract_id'=>$contract_id,'is_Discount'=>$type))
                ->field('id,telphone,username,rq_contract_equipment.contract_id,equipments_number,is_Discount,Discount_time,Discount_people')
                ->select();
            foreach ($temp as $k=>&$v){
//                $v['telphone'] = $val['telphone'];
                $v['contract_no'] = $val['contract_no'];
            }
            $data[] = $temp;
        }
        //计算有效设备数
        foreach ($cInfo as $key=>$val){
            $contract_id = $val['contract_id'];
            $temp =  db('contract_equipment')->where(array('contract_id'=>$contract_id,'is_Discount'=>0))->count();
            $count +=$temp;
        }
        $admin=0;//判断是否为行政中心
        //显示备注 操作记录 当前用户所有记录
        $userR=db('recommend_equipment_log')->where(array('uid'=>$cid))->select();
        $this->assign('count',$count);
        $this->assign('type',$type);
        $this->assign('cid',$cid);
        $this->assign('isAdmin',$admin);
        $this->assign('userR',$userR);
        $this->assign('list',$data);
        return $this->fetch('admin@customer:device_details');

    }

    /**
     * 导入用户数据
     * @author 蒙明然
     * @param Request $request
     * @return mixed|void
     */
        //上传excel到数据库
    public function addOldUser(Request $request)
    {
        $a_id=input('administrative');
        $relationship=input('relationship');        
        $province_id=input('province');
        $province_name=db('areas')->where('id',$province_id)->value('area_name');
        $city_id=input('city');
        $city_name=db('areas')->where('id',$city_id)->value('area_name');
        $area_id=input('area');
        $area_name=db('areas')->where('id',$area_id)->value('area_name');
        $o_id=db('administrative_info')->where(['a_id'=>$a_id])->value('operation');//获取o_id
        $aData=db('administrative_info')->where(array('a_id'=>$a_id))->find();
        $hadCus='';//判断用户是否存在
        $file = $request->file('excel');
        if($file){
            //上传到public/customer下
            $info = $file->move(ROOT_PATH . 'public' . DS . 'customer');
            if($info)
            {
                $savename = $info->getSaveName();
                $data=$this->Customer->addOldUser(ROOT_PATH . 'public' . DS . 'customer'. DS .$savename);
            // DB::startTrans();
            // try{
                if($relationship==1){//关系为家用客户
                    for ($k=2; $k<=count($data) ; $k++) {
                        $hadCus=$data[$k][0];
                        $where['id_card']=$data[$k][7];
                        $where['is_delete']=0;
                        $where['operation_id']=$o_id;
                        $checkRe=db('customer')->where($where)->find();
                        $checkNo=db('contract')->where(array('contract_no'=>$data[$k][1]))->find();
                        if($checkNo){
                            return $this->error($data[$k][1].' 合同已存在，请勿重复导入！');
                        }
                        if(!$checkRe){
                            for ($i=2; $i <=count($data) ; $i++) {
                                //Excel数据
                                $equipments_id=db('equipments')->where(array('equipments_name'=>$data[$i][9]))->value('equipments_id');
                                if($equipments_id)
                                {
                                    $idata['username']=$data[$i][0];
                                    $idata['add_time']=strtotime($data[$i][11]);
                                    if(strlen($data[$i][4])>11){
                                        return $this->error($data[$i][0].' 客户电话号码过长，请检查！');
                                    }
                                    $idata['telphone']=$data[$i][4];
                                    $idata['id_card']=$data[$i][7];
                                    $idata['user_address']=$data[$i][5];
                                    $idata['birthday']=strtotime($data[$i][6]);
                                    if($data[$i][2]=='普通客户'){
                                        $idata['nature']=2;
                                        $idata['user_type']=1;
                                    }if($data[$i][2]=='VIP客户')
                                {
                                    $idata['nature']=2;
                                    $idata['user_type']=2;
                                }
                                    if($data[$i][8]=='公租房'){
                                        $idata['housing_property']=1;
                                    }
                                    if($data[$i][8]=='廉租房'){
                                        $idata['housing_property']=2;
                                    }
                                    if($data[$i][8]=='租赁'){
                                        $idata['housing_property']=3;
                                    }
                                    //自定义数据
                                    $idata['avatar_img']=0;
                                    $idata['operation_id']=$o_id;
                                    $idata['province']=$province_name;
                                    $idata['province_code']=$province_id;
                                    $idata['city']=$city_name;
                                    $idata['area']=$area_name;
                                    $idata['city_code']=$city_id;
                                    $idata['area_code']=$area_id;
                                    // 插入数据并返回id
                                    $user_id=$this->Customer->insertOne($idata);
                                    if($user_id){
                                        //关联数据
                                        $c_a_info['user_id']=$user_id;
                                        $c_a_info['administrative_id']=$a_id;
                                        db('customer_administrative_center')->insert($c_a_info);//行政关联表
                                        //合同数据
                                        $cdata['contract_no']=$data[$i][1];//合同编号
                                        $cdata['user_id']=$user_id;
                                        $cdata['administrative_id']=$a_id;
                                        $cdata['operation_id']=$o_id;
                                        $cdata['province']=$province_name;
                                        $cdata['province_code']=$province_id;
                                        $cdata['city']=$city_name;
                                        $cdata['area']=$area_name;
                                        $cdata['city_code']=$city_id;
                                        $cdata['area_code']=$area_id;
                                        $cdata['contract_money']=$data[$i][12];//合同费用
                                        $cdata['phone']=$data[$i][4];
                                        $cdata['contact_person']=$data[$i][0];
                                        $cdata['installed_time']=strtotime($data[$i][11]);
                                        $cdata['address']=$data[$i][5];
                                        $cdata['for_core_cost']=$data[$i][10];
                                        $cdata['for_core_cycle']=6;//换芯周期
                                        $cdata['for_core_time']=strtotime($data[$i][13]);
                                        $cdata['recent_time']=strtotime($data[$i][13]);
                                        $cdata['contract_imgs']=0;
                                        $cdata['add_time']=strtotime($data[$i][11]);
                                        $cdata['nature']=2;//家用客户
                                        if($data[$i][8]=='公租房'){
                                            $cdata['housing_property']=1;
                                        }
                                        if($data[$i][8]=='廉租房'){
                                            $cdata['housing_property']=2;
                                        }
                                        if($data[$i][8]=='租赁'){
                                            $cdata['housing_property']=3;
                                        }
                                        $cdata['exire_date']=strtotime($data[$i][14])+31536000;//合同到期时间，续费时间
                                        $cdata['renew_date']=1;
                                        $cdata['receipt']=$data[$i][15];//收据编号
                                        $cdata['water_pressure']=$data[$i][16];//水压
                                        $con_id=db('contract')->insertGetId($cdata);
                                        //合同关联设备数据
                                        if($con_id){
                                            $c_eData['contract_id']=$con_id;
                                            $c_eData['equipments_id']=$equipments_id;
                                            $c_eData['equipments_number']=randEquipmentsNumber();//产生设备编号
                                            $c_eData['equipments_address']=$data[$i][5];
                                            $c_eData['addtime']=time();
                                            $c_eData['uptime']=time();
                                            $e_pdata['parts_id']=5;//默认：PP棉+颗粒活性炭+压缩炭+RO膜+椰壳活性炭
                                            $e_pdata['contract_id']=$con_id;
                                            $e_pdata['equipments_id']=$equipments_id;
                                            db('equipments_parts')->insert($e_pdata);//原始配件
                                            db('contract_equipment')->insert($c_eData);
                                        }
                                    }
                                }
                            }//for循环结束
                            return $this->success('导入成功！');
                        }
                        if($checkRe){
                            for ($i=2; $i <=count($data) ; $i++) {
                                //Excel数据
                                $equipments_id = db('equipments')->where(array('equipments_name' => $data[$i][9]))->value('equipments_id');
                                if ($equipments_id) {
                                    if ($checkRe['user_id']) {
                                        //合同数据
                                        $cdata['contract_no'] = $data[$i][1];//合同编号
                                        $cdata['user_id'] = $checkRe['user_id'];
                                        $cdata['administrative_id'] = $a_id;
                                        $cdata['operation_id'] = $o_id;
                                        $cdata['province'] = $province_name;
                                        $cdata['province_code'] = $province_id;
                                        $cdata['city'] = $city_name;
                                        $cdata['area'] = $area_name;
                                        $cdata['city_code'] = $city_id;
                                        $cdata['area_code'] = $area_id;
                                        $cdata['contract_money'] = $data[$i][12];//合同费用
                                        $cdata['phone'] = $data[$i][4];
                                        $cdata['contact_person'] = $data[$i][0];
                                        $cdata['installed_time'] = strtotime($data[$i][11]);
                                        $cdata['address'] = $data[$i][5];
                                        $cdata['for_core_cost'] = $data[$i][10];
                                        $cdata['for_core_cycle'] = 6;//换芯周期
                                        $cdata['for_core_time'] = strtotime($data[$i][13]);
                                        $cdata['recent_time'] = strtotime($data[$i][13]);
                                        $cdata['contract_imgs'] = 0;
                                        $cdata['add_time'] = strtotime($data[$i][11]);
                                        $cdata['nature'] = 2;//家用客户
                                        if ($data[$i][8] == '公租房') {
                                            $cdata['housing_property'] = 1;
                                        }
                                        if ($data[$i][8] == '廉租房') {
                                            $cdata['housing_property'] = 2;
                                        }
                                        if ($data[$i][8] == '租赁') {
                                            $cdata['housing_property'] = 3;
                                        }
                                        $cdata['exire_date'] = strtotime($data[$i][14]) + 31536000;//合同到期时间，续费时间
                                        $cdata['renew_date'] = 1;
                                        $cdata['receipt'] = $data[$i][15];//收据编号
                                        $cdata['water_pressure'] = $data[$i][16];//水压
                                        $con_id = db('contract')->insertGetId($cdata);
                                        //合同关联设备数据
                                        if ($con_id) {
                                            $c_eData['contract_id'] = $con_id;
                                            $c_eData['equipments_id'] = $equipments_id;
                                            $c_eData['equipments_number'] = randEquipmentsNumber();//产生设备编号
                                            $c_eData['equipments_address'] = $data[$i][5];
                                            $c_eData['addtime'] = time();
                                            $c_eData['uptime'] = time();
                                            $e_pdata['parts_id'] = 5;//默认：PP棉+颗粒活性炭+压缩炭+RO膜+椰壳活性炭
                                            $e_pdata['contract_id'] = $con_id;
                                            $e_pdata['equipments_id'] = $equipments_id;
                                            db('equipments_parts')->insert($e_pdata);//原始配件
                                            db('contract_equipment')->insert($c_eData);
                                        }
                                    }
                                }
                            }
                            return $this->success('导入成功！');
                        }
                    }
                }//家用客户关系结束
                if($relationship==4){//关系为商用客户
                    for ($k=2; $k<=count($data) ; $k++) {
                        $hadCus=$data[$k][3];
                        $where['id_card']=$data[$k][6];
                        $where['is_delete']=0;
                        $where['operation_id']=$o_id;
                        $checkRe=db('customer')->where($where)->find();
                        $checkNo=db('contract')->where(array('contract_no'=>$data[$k][1]))->find();
                        if($checkNo){
                            return $this->error($data[$k][1].' 合同已存在，请勿重复导入！');
                        }
                        if($checkRe){
                            for ($i=2; $i <=count($data) ; $i++) {
                                //合同数据
                                $cdata['contract_no']=$data[$i][1];//合同编号
                                $cdata['user_id']=$checkRe['user_id'];
                                $cdata['administrative_id']=$a_id;
                                $cdata['operation_id']=$o_id;
                                $cdata['province']=$province_name;
                                $cdata['province_code']=$province_id;
                                $cdata['city']=$city_name;
                                $cdata['area']=$area_name;
                                $cdata['city_code']=$city_id;
                                $cdata['area_code']=$area_id;
                                $cdata['contract_money']=$data[$i][11];//合同费用
                                $cdata['phone']=$data[$i][4];
                                $cdata['contact_person']=$data[$i][3];
                                $cdata['installed_time']=strtotime($data[$i][10]);
                                $cdata['address']=$data[$i][5];
                                $cdata['for_core_cost']=$data[$i][9];
                                $cdata['for_core_cycle']=3;//换芯周期
                                $cdata['for_core_time']=strtotime($data[$i][12]);
                                $cdata['recent_time']=strtotime($data[$i][12]);
                                $cdata['contract_imgs']=0;
                                $cdata['add_time']=strtotime($data[$i][10]);
                                $cdata['nature']=1;//商用客户
                                $cdata['corporate_name']=$data[$i][0];//公司名称
                                $cdata['receipt']=$data[$i][14];//收据编号
                                $cdata['water_pressure']=$data[$i][15];//水压
                                if($data[$i][8]=='公租房'){
                                    $cdata['housing_property']=1;
                                }
                                if($data[$i][8]=='廉租房'){
                                    $cdata['housing_property']=2;
                                }
                                if($data[$i][8]=='租赁'){
                                    $cdata['housing_property']=3;
                                }
                                $cdata['exire_date']=strtotime($data[$i][13])+31536000;//合同到期时间，续费时间
                                $cdata['renew_date']=1;
                                $con_id=db('contract')->insertGetId($cdata);
                                //合同关联设备数据
                                if($con_id){
                                    $c_eData['contract_id']=$con_id;
                                    $t=db('equipments')->where(array('equipments_name'=>$data[$i][8]))->find();
                                    $c_eData['equipments_id']=$t['equipments_id'];
                                    $c_eData['equipments_number']=randEquipmentsNumber();//产生设备编号
                                    $c_eData['equipments_address']=$data[$i][5];
                                    $c_eData['addtime']=time();
                                    $c_eData['uptime']=time();

                                    $e_pdata['parts_id']=5;//默认：PP棉+颗粒活性炭+压缩炭+RO膜+椰壳活性炭
                                    $e_pdata['contract_id']=$con_id;
                                    $e_pdata['equipments_id']=$t['equipments_id'];
                                    db('equipments_parts')->insert($e_pdata);
                                    db('contract_equipment')->insert($c_eData);
                                }
                            }//for循环结束
                            return $this->success('导入成功！');
                        }else{
                            return $this->error('客户 '.$hadCus.' 不存在，导入失败！');
                        }
                    }
                }//商用客户结束
                if($relationship==2){//关系为入职客户
                    for ($k=2; $k<=count($data) ; $k++) {
                        $hadCus=$data[$k][0];
                        $where['id_card']=$data[$k][1];
                        $where['is_delete']=0;
                        $where['operation_id']=$o_id;
                        $checkRe=db('customer')->where($where)->find();
                        if($checkRe['is_entry']=1){
                            return $this->error('客户 '.$hadCus.' 已入职，请勿重复导入！');
                        }
                        if($checkRe){
                            for ($i=2; $i <=count($data) ; $i++) {
                                $Iwhere['user_id']=$checkRe['user_id'];
                                $Cdata['is_entry']=1;
                                $Cdata['employee_id']=EmployeeNumber();
                                $num['personnel_number']=$Cdata['employee_id'];
                                $num['personnel_time']=strtotime($data[$k][2]);
                                db('personnel_number')->insert($num);
                                db('customer')->where($Iwhere)->update($Cdata);
                            }
                            return $this->success('导入成功！请到结业系统完成其他手续！');
                        }else{
                            return $this->error('客户 '.$hadCus.' 不存在，不能办理入职！');
                        }
                    }
                }//入职客户结束
                if($relationship==3){//推荐关系
                    for ($k=2; $k<=count($data) ; $k++) {
                        $where['telphone']=$data[$k][1];
                        $where['is_delete']=0;
                        $where['operation_id']=$o_id;
                        $checkRe=db('customer')->where($where)->find();//获取转介绍人信息
                        $checkAp=db('customer')->where(['telphone'=>$data[$k][2],'is_delete'=>0])->find();//业务申请人
                        $checkUs=db('customer')->where(array('telphone'=>$data[$k][0],'is_delete'=>0))->find();//客户信息
                        $checkTJ=db('contract')->where(array('user_id'=>$checkUs['user_id'],'is_delete'=>0,'contract_no'=>$data[$k][3]))->find();//合同下的所有设备
                        $checkATJ=db('contract')->where(array('user_id'=>$checkAp['user_id'],'is_delete'=>0,'contract_no'=>$data[$k][3]))->find();
                        if($checkTJ['introducer_id']!=null||!empty($checkTJ['introducer_id'])||$checkTJ['introducer_id']!=''){
                            return $this->error('客户 '.$data[$k][0].' 已经存在转介绍人！');
                        }
                        if($checkATJ['applicant']!=0){
                            return $this->error('客户 '.$data[$k][0].' 已经存在业务申请人！');
                        }
                        if(!$checkUs){
                            return $this->error('客户 '.$hadCus.' 不存在，请检查！');
                        }
                        if($checkRe){
                            for ($i=2; $i <=count($data) ; $i++) {
                                $Cdata['introducer_id']=$checkRe['user_id'];//转介绍人id
                                $Cdata['applicant']=$checkAp['user_id'];//申请人ID
                                db('contract')->where(array('contract_no'=>$data[$k][3]))->update($Cdata);//写入合同
                                //寻找上家
                                $upHomePeople = $this->Customer->seekPushPeople($checkRe['user_id'],$checkUs['user_id']);//推荐人，被推荐人
                                db('customer')->where(array('user_id'=>$checkRe['user_id']))->setInc('number', 1);
                                //推荐人上属单位获得团队推荐台数
                                if (!empty($upHomePeople)){
                                    foreach ($upHomePeople as $upHomePeopleKey=>$upHomePeopleValue){
                                        $where = [
                                            'user_id'=>$upHomePeopleValue['user_id']
                                        ];
                                        $this->Customer->incTeamEquipmentNumber($where,'team_number',1);
                                    }
                                }

                            }
                            return $this->success('导入成功！');
                        }else{
                            return $this->error('推荐人 '.$hadCus.' 不存在，请检查！');
                        }
                    }
                }//推荐关系结束
            //     Db::commit();
            // } catch (\Exception $e) {
            //         Db::rollback();
            //         return $this->success('导入失败！');
            //     }


            }else{
                return $file->getError();
            }
        }
    }
}



